#include "stdafx.h"
#include "QuestionLibrary.h"
#include "LoginHelper.h"
#include "Logger.h"

#include "Poco\File.h"
#include "Poco\Exception.h"
#include "Poco\Format.h"
#include "Poco\Timezone.h"
#include "Poco\DateTimeFormatter.h"
#include "Poco\Data\Statement.h"
#include "Poco\Data\RecordSet.h"
#include "Poco\Data\Binding.h"
#include "Poco\Data\SQLite\Connector.h"

#include <assert.h>

namespace ExamSystem
{
    using Poco::DateTime;
    using Poco::Timezone;
    using Poco::Exception;
    using Poco::Data::now;
    using Poco::Data::into;
    using Poco::Data::range;
    using Poco::Data::use;
    using Poco::Data::Statement;
    using Poco::Data::RecordSet;

    CQuestionLibrary::QuestionLoader CQuestionLibrary::ms_questionLoaders[QuestionTypeCount] = {
        &CQuestionLibrary::LoadChoiceQuestion,
        &CQuestionLibrary::LoadFillingQuestion,
        &CQuestionLibrary::LoadTypingQuestion,
        &CQuestionLibrary::LoadQNAQuestion,
        &CQuestionLibrary::LoadOfficeQuestion,
        &CQuestionLibrary::LoadCodingQuestion,
        &CQuestionLibrary::LoadHtmlQuestion,
        &CQuestionLibrary::LoadPhotoshopQuestion
    };

    CQuestionLibrary::CriterionLoader CQuestionLibrary::ms_criterionLoaders[QuestionTypeCount] = {
        &CQuestionLibrary::LoadChoiceCriterion,
        &CQuestionLibrary::LoadFillingCriterion,
        &CQuestionLibrary::LoadTypingCriterion,
        &CQuestionLibrary::LoadQNACriterion,
        &CQuestionLibrary::LoadOfficeCriterion,
        &CQuestionLibrary::LoadCodingCriterion,
        &CQuestionLibrary::LoadHtmlCriterion,
        &CQuestionLibrary::LoadPhotoshopCriterion
    };

    CQuestionLibrary::QuestionSaver CQuestionLibrary::ms_questionSavers[QuestionTypeCount] = {
        &CQuestionLibrary::SaveChoiceQuestion,
        &CQuestionLibrary::SaveFillingQuestion,
        &CQuestionLibrary::SaveTypingQuestion,
        &CQuestionLibrary::SaveQNAQuestion,
        &CQuestionLibrary::SaveOfficeQuestion,
        &CQuestionLibrary::SaveCodinglQuestion,
        &CQuestionLibrary::SaveHtmlQuestion,
        &CQuestionLibrary::SavePhotoshopQuestion
    };

    //const std::string CQuestionLibrary::gQuestionTypeNames[QuestionTypeCount + 1] = {
    //    "choice", "filling", "typing", "qna", "office", "coding", "html", "photoshop", "library_status"
    //};

    const std::string CQuestionLibrary::blank = "@blank@";
    const std::string CQuestionLibrary::separator = "@sep@";

    CQuestionLibrary::CQuestionLibrary(void)
        : m_flagInitialized(false)
    {

    }

    CQuestionLibrary::~CQuestionLibrary(void)
    {

    }

    bool CQuestionLibrary::Initialize(const std::string &databaseFile, bool create /*= false*/)
    {
        if (m_flagInitialized && databaseFile == m_databaseFile)
            return true;

        //m_flagInitialized = false;

        Poco::File file(databaseFile);
        if (!file.exists() && !create)
        {
            systemLogger.error("Database " + databaseFile + " doesn't exist.");
            return false;
        }
        m_databaseFile = databaseFile;

        try
        {
            // register SQLite connector
            Poco::Data::SQLite::Connector::registerConnector();

            // create a session
            m_session = new Session("SQLite", m_databaseFile);
            if (!m_session->isConnected())
                throw Poco::Exception("Failed to open " + m_databaseFile);

            // check if there are all tables
            std::vector<std::string> names;
            (*m_session) << "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
                into(names),
                now;

            if (names.size() != QuestionTypeCount + 1 && !CreateDatabase())                
                return false;

            m_flagInitialized = true;
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            m_session = NULL;
            m_databaseFile.clear();
            m_flagInitialized = false;
            return false;
        }
    }

    bool CQuestionLibrary::CreateDatabase(void)
    {
        try
        {
            assert(m_session);
            Session &session = *m_session;

            // choice
            session << "CREATE TABLE IF NOT EXISTS "
                "choice("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "options NTEXT NOT NULL, "
                    "criterion INTEGER NOT NULL"
                    ")", now;
            // filling
            session << "CREATE TABLE IF NOT EXISTS "
                "filling("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "criterion NTEXT NOT NULL"
                    ")", now;
            // typing
            session << "CREATE TABLE IF NOT EXISTS "
                "typing("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "time INTEGER NOT NULL,"
                    "chinese INTEGER NOT NULL"
                    ")", now;
            // question and answer
            session << "CREATE TABLE IF NOT EXISTS "
                "qna("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL,"
                    "criterion NTEXT NOT NULL"
                    ")", now;
            // office
            session << "CREATE TABLE IF NOT EXISTS "
                "office("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "file NTEXT, " // base64 encoded
                    "default_file INTEGER, "
                    "answer_files NTEXT, " 
                    "criterion NTEXT"
                    ")", now;
            // coding
            session << "CREATE TABLE IF NOT EXISTS "
                "coding("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "file NTEXT, " // base64 encoded
                    "default_file INTEGER, "
                    "answer_files NTEXT, " 
                    "criterion"  // input and output files
                    ")", now;
            // html
            session << "CREATE TABLE IF NOT EXISTS "
                "html("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "file NTEXT, " // base64 encoded
                    "default_file INTEGER, "
                    "answer_files NTEXT, " 
                    "criterion NTEXT"
                    ")", now;
            // photoshop
            session << "CREATE TABLE IF NOT EXISTS "
                "photoshop("
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "title NTEXT NOT NULL, "
                    "file NTEXT, " // base64 encoded
                    "default_file INTEGER, "
                    "answer_files NTEXT" 
                    ")", now;
            // status
            session << "CREATE TABLE IF NOT EXISTS "
                "library_status("
                    "field CHAR(10) PRIMARY KEY, "
                    "value VARCHAR"
                    ")", now;
            session << "DELETE FROM library_status", now;

            // update status
            std::string field;
            Statement status(session);
            status << "INSERT INTO library_status VALUES(?, '')",
                use(field);

            field = "version";      status.execute();
            field = "choice";       status.execute();
            field = "filling";      status.execute();
            field = "typing";       status.execute();
            field = "qna";          status.execute();
            field = "office";       status.execute();
            field = "coding";       status.execute();
            field = "html";         status.execute();
            field = "photoshop";    status.execute();
            return UpdateLibraryStatus();
            
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::UpdateLibraryStatus(unsigned int mask /*= -1*/)
    {
        try
        {
            assert(m_session);
            Session &session = *m_session;

            std::string field;
            
            for (int i = 0; i < QuestionTypeCount; i++)
            {
                if ((mask & (1 << i)) == 0)
                    continue;

                int count;
                session << Poco::format("SELECT COUNT(id) FROM %s",
                        gQuestionTypeNames[i]),
                    into(count), now;

                session << Poco::format("UPDATE library_status SET value='%d' WHERE field='%s'",
                    count, gQuestionTypeNames[i]), now;
            }

            // version
            //if (mask & (1 << QuestionTypeCount))
            // the version should be updated every time
            {
                Poco::DateTime dateTime;
                dateTime.makeLocal(Poco::Timezone::utcOffset());
                std::string str = Poco::DateTimeFormatter::format(dateTime, "%Y-%m-%d %H:%M:%S");
                session << Poco::format("UPDATE library_status SET value='%s' WHERE field='version'",
                    str),
                    now;
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }   
    }

    std::string CQuestionLibrary::GetDatabaseVersion(void)
    {
        if (!m_session)
            return "";

        std::string version;
        try
        {
            (*m_session) << "SELECT value FROM library_status WHERE field='version'",
                into(version),
                range(0, 1),
                now;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }   

        return version;
    }

    int CQuestionLibrary::GetQuestionCount(QuestionType type)
    {
        if (!m_session)
            return -1;

        int count;
        try 
        {
            (*m_session) << "SELECT value FROM library_status WHERE field=?",
                use(gQuestionTypeNames[type]),
                into(count),
                now;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return -1;
        }

        return count;
    }

    int CQuestionLibrary::GetMaxQuestionID(QuestionType type)
    {
        if (!m_session)
            return -1;

        int maxID;
        try 
        {
            (*m_session) << Poco::format("SELECT MAX(id) FROM %s",
                gQuestionTypeNames[type]),
                into(maxID),
                now;

            return maxID;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return -1;
        }
    }

    bool CQuestionLibrary::LoadQuestion(CQuestion *question)
    {
        if (!m_session)
            return false;

        if (!question)
            return false;

        return (this->*ms_questionLoaders[question->Type()])(question);
    }

    bool CQuestionLibrary::LoadCriterion(CCriterion *criterion)
    {
        if (!m_session)
            return false;

        if (!criterion)
            return false;

        return (this->*ms_criterionLoaders[criterion->Type()])(criterion);
    }

    bool CQuestionLibrary::LoadAllQuestion(QuestionType type, std::vector<QuestionBase> &questions, int start /*= 0*/, int end /*= -1*/)
    {
        if (!m_session)
            return false;

        try
        {
            questions.clear();
            std::vector<int> ids;
            std::vector<std::string> titles;
            (*m_session) << "SELECT id,title FROM " << gQuestionTypeNames[type],
                into(ids), into(titles), range(start, end), now;

            questions.resize(ids.size());
            for (size_t i = 0; i < ids.size(); i++)
            {
                questions[i].questionID = ids[i];
                questions[i].questionTitle = titles[i];
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadAllQuestion(QuestionType type, std::vector<int> &questionIds)
    {
        if (!m_session)
            return false;

        try
        {
            questionIds.clear();
            (*m_session) << "SELECT id FROM " << gQuestionTypeNames[type],
                into(questionIds), now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::SaveQuestion(CQuestion *question, CCriterion *criterion)
    {
        if (!question || !criterion || 
            question->Type() != criterion->Type() ||
            question->questionID != criterion->questionID)
            return false;

        if ((this->*ms_questionSavers[question->Type()])(question, criterion))
            return UpdateLibraryStatus(1 << question->Type());
        return false;
    }

    void CQuestionLibrary::SeparateString(const std::string &str, std::vector<std::string> &segments)
    {
        segments.clear();
        size_t pos = str.find(separator);
        size_t lastPos = 0;
        while (pos != str.npos)
        {
            segments.push_back(str.substr(lastPos, pos - lastPos));
            lastPos = pos + separator.size();
            pos = str.find(separator, lastPos);
        }

        if (lastPos < str.size())
            segments.push_back(str.substr(lastPos));
        else if (lastPos == str.size())
            segments.push_back("");
    }

    std::string CQuestionLibrary::AssembleString(const std::vector<std::string> &segments)
    {
        if (segments.empty())
            return "";

        std::string str = segments.front();
        for (size_t i = 1; i < segments.size(); i++)
            str += separator + segments[i];

        return str;
    }

    bool CQuestionLibrary::LoadQuestionTitle(CQuestion *question)
    {
        try
        {
            (*m_session) << Poco::format("SELECT title FROM %s WHERE id=%d", 
                gQuestionTypeNames[question->Type()],
                question->questionID),
                into(question->questionTitle), now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadChoiceQuestion(CQuestion *question)
    {
        CChoiceQuestion &q = *static_cast<CChoiceQuestion *>(question);

        try
        {
            std::string options;

            (*m_session) << Poco::format("SELECT title,options FROM %s WHERE id=%d", 
                    gQuestionTypeNames[q.Type()],
                    question->questionID),
                into(q.questionTitle), into(options), now;

            SeparateString(options, q.questionOptions);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadFillingQuestion(CQuestion *question)
    {
        return LoadQuestionTitle(question);
    }

    bool CQuestionLibrary::LoadTypingQuestion(CQuestion *question)
    {
        CTypingQuestion &q = *static_cast<CTypingQuestion *>(question);

        try
        {
            (*m_session) << Poco::format("SELECT title,time,chinese FROM %s WHERE id=%d", 
                gQuestionTypeNames[q.Type()],
                question->questionID),
                into(question->questionTitle),
                into(q.questionTime),
                into(q.flagChineseTyping),
                now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadQNAQuestion(CQuestion *question)
    {
        return LoadQuestionTitle(question);
    }

    bool CQuestionLibrary::LoadOfficeQuestion(CQuestion *question)
    {
        return LoadFiledQuestion(question);
    }

    bool CQuestionLibrary::LoadCodingQuestion(CQuestion *question)
    {
        return LoadFiledQuestion(question);
    }

    bool CQuestionLibrary::LoadHtmlQuestion(CQuestion *question)
    {
        return LoadFiledQuestion(question);
    }

    bool CQuestionLibrary::LoadPhotoshopQuestion(CQuestion *question)
    {
        return LoadFiledQuestion(question);
    }

    bool CQuestionLibrary::LoadFiledQuestion(CQuestion *question)
    {
        CFiledQuestion &q = *dynamic_cast<CFiledQuestion *>(question);

        try
        {
            std::string answerFile;
            std::string file;
            (*m_session) << Poco::format("SELECT title,file,default_file,answer_files FROM %s WHERE id=%d", 
                gQuestionTypeNames[q.Type()],
                question->questionID),
                into(question->questionTitle),
                into(file),
                into(q.questionStartFileIdx),
                into(answerFile),
                now;

            q.questionFile.SetEncodedString(file);
            SeparateString(answerFile, q.answerFiles);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }   
    }

    // savings
    bool CQuestionLibrary::SaveChoiceQuestion(CQuestion *question, CCriterion *criterion)
    {
        CChoiceQuestion &q = *static_cast<CChoiceQuestion *>(question);
        CChoiceCriterion &c = *static_cast<CChoiceCriterion *>(criterion);
        try
        {
            //if (q.questionID >= 0)  // update
            //{
            //    (*m_session) << Poco::format("UPDATE %s SET title='%s', options='%s', criterion=%d WHERE id=%d",
            //        gQuestionTypeNames[q.Type()], 
            //        q.questionTitle, 
            //        AssembleString(q.questionOptions),
            //        c.criterionChoice, 
            //        q.questionID),
            //        now;
            //}
            //else // insert now
            {
                if (q.questionID < 0)
                    q.questionID = GetMaxQuestionID(q.Type()) + 1;

                (*m_session) << Poco::format("INSERT OR REPLACE INTO %s VALUES(?, ?, ?, ?)",
                    gQuestionTypeNames[q.Type()]),
                    use(q.questionID),
                    use(q.questionTitle),
                    use(AssembleString(q.questionOptions)),
                    use(c.criterionChoice), 
                    now;
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::SaveFillingQuestion(CQuestion *question, CCriterion *criterion)
    {
        CFillingQuestion &q = *static_cast<CFillingQuestion *>(question);
        CFillingCriterion &c = *static_cast<CFillingCriterion *>(criterion);
        try
        {
            //if (q.questionID >= 0)  // update
            //{
            //    (*m_session) << Poco::format("UPDATE %s SET title='%s', criterion='%s' WHERE id=%d",
            //        gQuestionTypeNames[q.Type()], 
            //        q.questionTitle, 
            //        AssembleString(c.critierionBlanks), 
            //        q.questionID),
            //        now;
            //}
            //else // insert now
            {
                if (q.questionID < 0)
                    q.questionID = GetMaxQuestionID(q.Type()) + 1;

                (*m_session) << Poco::format("INSERT OR REPLACE INTO %s VALUES(?, ?, ?)",
                    gQuestionTypeNames[q.Type()]),
                    use(q.questionID),
                    use(q.questionTitle),
                    use(AssembleString(c.critierionBlanks)), 
                    now;
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::SaveTypingQuestion(CQuestion *question, CCriterion *criterion)
    {
        CTypingQuestion &q = *static_cast<CTypingQuestion *>(question);
        CTypingCriterion &c = *static_cast<CTypingCriterion *>(criterion);
        try
        {
            //if (q.questionID >= 0)  // update
            //{
            //    (*m_session) << Poco::format("UPDATE %s SET title='%s',time=%u,chinese=%u WHERE id=%d",
            //        gQuestionTypeNames[q.Type()], 
            //        q.questionTitle, 
            //        q.questionTime, 
            //        q.flagChineseTyping,
            //        q.questionID),
            //        now;
            //}
            //else // insert now
            {
                if (q.questionID < 0)
                    q.questionID = GetMaxQuestionID(q.Type()) + 1;

                (*m_session) << Poco::format("INSERT OR REPLACE INTO %s VALUES(?, ?, ?, ?)",
                    gQuestionTypeNames[q.Type()]),
                    use(q.questionID),
                    use(q.questionTitle),
                    use(q.questionTime), 
                    use(q.flagChineseTyping),
                    now;
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::SaveQNAQuestion(CQuestion *question, CCriterion *criterion)
    {
        CQNAQuestion &q = *static_cast<CQNAQuestion *>(question);
        CQNACriterion &c = *static_cast<CQNACriterion *>(criterion);
        try
        {
            //if (q.questionID >= 0)  // update
            //{
            //    (*m_session) << Poco::format("UPDATE %s SET title='%s',criterion='%s' WHERE id=%d",
            //        gQuestionTypeNames[q.Type()], 
            //        q.questionTitle, 
            //        c.criterionText,
            //        q.questionID),
            //        now;
            //}
            //else // insert now
            {
                if (q.questionID < 0)
                    q.questionID = GetMaxQuestionID(q.Type()) + 1;

                (*m_session) << Poco::format("INSERT OR REPLACE INTO %s VALUES(?, ?, ?)",
                    gQuestionTypeNames[q.Type()]),
                    use(q.questionID),
                    use(q.questionTitle),
                    use(c.criterionText),
                    now;
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::SaveOfficeQuestion(CQuestion *question, CCriterion *criterion)
    {
        if (SaveFiledQuestion(question))
        {
            criterion->questionID = question->questionID;
            return SavePropertyCriternion(criterion);
        }
        return false;
    }

    bool CQuestionLibrary::SaveCodinglQuestion(CQuestion *question, CCriterion *criterion)
    {
        if (SaveFiledQuestion(question))
        {
            criterion->questionID = question->questionID;
            return SaveFiledCriterion(criterion);
        }
        return false;
    }

    bool CQuestionLibrary::SaveHtmlQuestion(CQuestion *question, CCriterion *criterion)
    {
        if (SaveFiledQuestion(question))
        {
            criterion->questionID = question->questionID;
            return SavePropertyCriternion(criterion);
        }
        return false;
    }

    bool CQuestionLibrary::SavePhotoshopQuestion(CQuestion *question, CCriterion *criterion)
    {
        return SaveFiledQuestion(question, false);
    }

    bool CQuestionLibrary::SaveFiledQuestion(CQuestion *question, bool withCriternion /*= true*/)
    {
        CFiledQuestion &q = *dynamic_cast<CFiledQuestion *>(question);
        try
        {
            if (q.questionID < 0)
                q.questionID = GetMaxQuestionID(q.Type()) + 1;

            std::string answerFile = AssembleString(q.answerFiles);

            if (withCriternion)
            {
                (*m_session) << Poco::format("INSERT OR REPLACE INTO %s VALUES(?, ?, ?, ?, ?, ?)",
                    gQuestionTypeNames[q.Type()]),
                    use(q.questionID),
                    use(q.questionTitle),
                    use(q.questionFile.GetEncodedString()),
                    use(q.questionStartFileIdx),
                    use(answerFile),
                    use(""),
                    now;
            }
            else
            {
                (*m_session) << Poco::format("INSERT OR REPLACE INTO %s VALUES(?, ?, ?, ?, ?)" ,
                    gQuestionTypeNames[q.Type()]),
                    use(q.questionID),
                    use(q.questionTitle),
                    use(q.questionFile.GetEncodedString()),
                    use(q.questionStartFileIdx),
                    use(answerFile),
                    now;
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }
    
    bool CQuestionLibrary::SaveFiledCriterion(CCriterion *criterion)
    {
        CFiledCriterion &c = *dynamic_cast<CFiledCriterion *>(criterion);
        try
        {
            if (c.questionID < 0)
                return false;

            (*m_session) << Poco::format("UPDATE %s SET criterion=\'%s\' WHERE id=%d",
                gQuestionTypeNames[c.Type()],
                c.criterionFile.GetEncodedString(),
                c.questionID),
                now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::SavePropertyCriternion(CCriterion *criterion)
    {
        CPropertyCriterion &c = *dynamic_cast<CPropertyCriterion *>(criterion);
        try
        {
            if (c.questionID < 0)
                return false;

            c.criterionValues.resize(c.criterionProperties.size());
            c.criterionWeights.resize(c.criterionProperties.size());

            std::string properties;

            if (!c.criterionProperties.empty())
            {
                properties += AssembleString(c.criterionProperties);
                properties += separator;
                properties += AssembleString(c.criterionValues);
                properties += separator;

                char buf[100];
                sprintf_s(buf, "%0.3f", c.criterionWeights.front());
                properties += buf;
                for (size_t i = 1; i < c.criterionWeights.size(); i++)
                {
                    sprintf_s(buf, "%s%0.3f", separator.c_str(), c.criterionWeights[i]);
                    properties += buf;
                }
            }

            (*m_session) << Poco::format("UPDATE %s SET criterion=\'%s\' WHERE id=%d",
                gQuestionTypeNames[c.Type()],
                properties,
                c.questionID),
                now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadChoiceCriterion(CCriterion *criterion)
    {
        CChoiceCriterion &c = *static_cast<CChoiceCriterion *>(criterion);

        try
        {
            std::string options;

            (*m_session) << Poco::format("SELECT criterion FROM %s WHERE id=%d", 
                gQuestionTypeNames[c.Type()],
                c.questionID),
                into(c.criterionChoice), now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadFillingCriterion(CCriterion *criterion)
    {
        CFillingCriterion &c = *static_cast<CFillingCriterion *>(criterion);

        try
        {
            std::string blanks;

            (*m_session) << Poco::format("SELECT criterion FROM %s WHERE id=%d", 
                gQuestionTypeNames[c.Type()],
                c.questionID),
                into(blanks), now;

            SeparateString(blanks, c.critierionBlanks);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadTypingCriterion(CCriterion *criterion)
    {
        CTypingCriterion &c = *static_cast<CTypingCriterion *>(criterion);

        try
        {
            (*m_session) << Poco::format("SELECT title FROM %s WHERE id=%d", 
                gQuestionTypeNames[c.Type()],
                c.questionID),
                into(c.criterionTyping), now;

            Poco::trimInPlace(c.criterionTyping);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
        return true;
    }

    bool CQuestionLibrary::LoadQNACriterion(CCriterion *criterion)
    {
        CQNACriterion &c = *static_cast<CQNACriterion *>(criterion);

        try
        {
            (*m_session) << Poco::format("SELECT criterion FROM %s WHERE id=%d", 
                gQuestionTypeNames[c.Type()],
                c.questionID),
                into(c.criterionText), now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::LoadOfficeCriterion(CCriterion *criterion)
    {
        return LoadPropertyCriterion(criterion);
    }

    bool CQuestionLibrary::LoadCodingCriterion(CCriterion *criterion)
    {
        return LoadFiledCriterion(criterion);
    }

    bool CQuestionLibrary::LoadHtmlCriterion(CCriterion *criterion)
    {
        return LoadPropertyCriterion(criterion);
    }

    bool CQuestionLibrary::LoadPhotoshopCriterion(CCriterion *criterion)
    {
        return true;
    }

    bool CQuestionLibrary::LoadFiledCriterion(CCriterion *criterion)
    {
        CFiledCriterion &c = *dynamic_cast<CFiledCriterion *>(criterion);

        try
        {
            std::string file;
            (*m_session) << Poco::format("SELECT criterion FROM %s WHERE id=%d", 
                gQuestionTypeNames[c.Type()],
                c.questionID),
                into(file),
                now;

            c.criterionFile.SetEncodedString(file);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }   
    }

    bool CQuestionLibrary::LoadPropertyCriterion(CCriterion *criterion)
    {
        CPropertyCriterion &c = *dynamic_cast<CPropertyCriterion *>(criterion);

        try
        {
            std::string text;
            (*m_session) << Poco::format("SELECT criterion FROM %s WHERE id=%d", 
                gQuestionTypeNames[c.Type()],
                c.questionID),
                into(text),
                now;

            std::vector<std::string> properties;
            if (!text.empty())
                SeparateString(text, properties);
            assert(properties.size() % 3 == 0);
            size_t count = properties.size() / 3;
            c.criterionProperties.resize(count);
            c.criterionValues.resize(count);
            c.criterionWeights.resize(count);
            for (size_t i = 0; i < count; i++)
            {
                c.criterionProperties[i] = properties[i];
                c.criterionValues[i] = properties[i + count];
                c.criterionWeights[i] = atof(properties[i + count * 2].c_str());
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }   
    }

    bool CQuestionLibrary::DeleteQuestion(QuestionType questionType, int questionID)
    {
        if (!m_session)
            return false;

        try 
        {
            (*m_session) << Poco::format("DELETE FROM %s WHERE id=%d",
                gQuestionTypeNames[questionType], questionID), now;

            return UpdateLibraryStatus(1 << questionType);
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CQuestionLibrary::DeleteQuestion(CQuestion *question)
    {
        if (!question)
            return false;
        return DeleteQuestion(question->Type(), question->questionID);
    }

    bool CQuestionLibrary::IsInitialized(void) const
    {
        return m_flagInitialized;
    }

    void CQuestionLibrary::Finalize(void)
    {
        if (!m_flagInitialized)
            return;

        m_session->close();
        m_session = NULL;

        m_flagInitialized = false;
    }


}